第4話 DAOとADOの違い【連載】実務で使えるAccessのコツ

データベース管理ソフトウェア、Accessを実務で使うときに「知っているとちょっと便利なコツ」について紹介するこのコラム。今回は、AccessVBAを使う方なら一度は考えるDAOとADOの違いと使い分けについて、記事公開時点（2021年8月）の最新の情報を踏まえて解説します。あなたのAccess力アップに役立ちますように。

前回のコラムを見逃した方はこちらからご覧ください。第3話 マクロとVBAの違い

  DAOとADOってなんのこと？　　　　

AccessVBAを使ってデータベースを操作しようとすると、DAOやADOという単語を耳にしませんか？　これは一体、何なのでしょうか？

ざっくり言うと、どちらもデータベースへ接続・操作する手段のことです。Accessでは、どちらも利用することができます。

今回は、この2つについて学んでいきましょう。

  どう違うの？　　　　

この2つは略称で、正式名称は以下のとおりです。

・DAO　…　Data Access Objects・ADO　…　ActiveX Data Object

DAOはAccessで使用されるデータベースに特化した手段です。それに対してADOは、Accessをはじめ各社の主要なデータベースでも幅広く使える接続手段です。

DAOに関しては、Access開発当初からずっと使われてきた技術だったものの、Access2000よりADOが使えるようになったことで、一時期はADOが推奨される風潮があったようです。

しかし、Access2007よりDAOが新しくなったことから、現在では「古い手段」というイメージは払拭されています。

  どちらを使うべき？　　　　

この記事が公開される2021年8月現在では、Accessにおいて、DAO/ADOどちらを使っても大きな差はありません。

DAOは元々Accessに特化した技術なのでADOよりも高速と言われていますが、パソコンのスペックもどんどん上がっているので、体感できるほどの差にはなりにくいと思われます。

Accessのみで完結するシステムならばDAO、Access以外でデータベースを使う・または今後その可能性がある場合はADO、という判断をしてもよいのではないでしょうか。

  テンプレート化して使うのがおすすめ　　　　

データベースを操作するには、SQLというデータベースに問い合わせを行うための言語を使います。

VBAからデータベースを操作する際、SQLは目的によって内容を変えなくてはなりませんが、そのSQLを「処理する部分」は一定です。DAO/ADOそれぞれ、処理部分のみ切り分けたテンプレートを用意しておけば、SQLのみの変更でかんたんに利用することができます。

ただし、SQLでの問い合わせには大きく分けて「データベースから任意のレコードを抽出する」働きと「データベースの内容を変更する」働きがあるので、VBAで「処理する部分」はDAO/ADOそれぞれに、2種類ずつ必要になります。

特に「任意のレコードを抽出する」場合、レコードセットというオブジェクトが登場します。これは、データベースのレコードが集まった表形式のデータ構造で、Accessでテーブルやクエリをデータシートビューで開いた時と同じイメージのものをVBA上で保持、扱うことができます。

後述のコードで、任意のレコードの集合を「レコードセット」という形で取得し、それを展開する記述がありますので、注目してみてください。

それではここからはDAO/ADOのテンプレートとして使える、SQLを「処理する部分」のAccessVBAをご紹介します。任意のテーブルが存在しているaccdbファイルを開いた状態からAlt+F11キーでVBE（Visual Basic Editor）を開きます。

「挿入」→「標準モジュール」を作成して、そこへコードをコピー&ペーストしてお使いください。

  DAOのテンプレート　　　　

それではまず、DAOを利用する方法から紹介します。

DAOは2021年8月現在ではAccessの規定のデータベース接続方法と考えられるため、標準で「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」にチェックが付いています。この設定でDAO.DatabaseやDAO.Recordsetなどのオブジェクトが利用できます。

以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。

On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言Set db = CurrentDb '現在開いているデータベースへ接続

'レコードセットのオープンDim rs As DAO.Recordset 'DAOレコードセットオブジェクト変数宣言Set rs = db.OpenRecordset(sql) 'カーソルタイプを指定してレコードセットを開く

'空だったら最終処理へIf rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合  getRsInArray_DAO = Array() '空の配列を返す  GoTo Finally '最終処理へジャンプ End If

'レコード数を取得Dim recordCount As Longrs.MoveLastrecordCount = rs.recordCountrs.MoveFirst

'配列に展開Dim ary() As VariantReDim ary(recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義Dim rsNum As Long '縦要素（レコードセット）の繰り返し用変数Dim fldNum As Long '横要素（フィールド）の繰り返し用変数Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す

getRsInArray_DAO = ary '配列を返す

GoTo Finally '正常に終了したら最終処理へジャンプ

Finally: '最終処理

以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行し、結果をTrue/Falseで返します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。

Dim ws As DAO.Workspace 'トランザクション用オブジェクトSet ws = DBEngine(0)

Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言Set db = CurrentDb '現在開いているデータベースへ接続

ws.BeginTrans 'トランザクション開始

'実行Dim sql As VariantFor Each sql In sqlList 'SQL文リストをループ  db.Execute sql '1行ずつ実行Next sql

ws.CommitTrans '確定

tryExecute_DAO = True '成功だった場合、関数の結果にTrueを入れるGoTo Finally '正常に終了したら最終処理へジャンプ

  ADOのテンプレート　　　　

続いてADOを利用する方法です。

DAOの場合、「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」を利用しましたが、ADOで同様にするには、「Microsoft ActiveX Data Objects X.X Library」を利用します。パソコン環境によってバージョンが複数存在する可能性がありますが、数字の大きなものがよいでしょう。

ただし、このライブラリはデフォルトでチェックがオフになっているので、VBAを組み込んだAccessファイルを別のパソコンで使うときに、参照設定でチェックを入れ直さないとエラーになってしまいます。

ライブラリを使ったほうが開発時は便利なのですが、運用時にうっかりエラーを引き起こす可能性もあるので、今回は参照設定の「Microsoft ActiveX Data Objects X.X Library」にチェックを入れずにADOを利用するコードをご紹介します。

以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。

Const adStateOpen = 1 'レコードセットが開いている場合の設定値Const adOpenKeyset = 1 'レコードセットカーソルタイプの設定値

Dim cn As Object 'ADOコネクション用オブジェクトの宣言Set cn = CurrentProject.Connection '現在開いているデータベースへ接続

'レコードセットのオープンDim rs As Object 'レコードセット用変数宣言 Set rs = CreateObject("ADODB.RecordSet") 'ADOレコードセットオブジェクトを作成rs.Open sql, cn, adOpenKeyset 'カーソルタイプを指定してレコードセットを開く

'空だったら最終処理へIf rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合  getRsInArray_ADO = Array() '空の配列を返す  GoTo Finally '最終処理へジャンプEnd If

'配列に展開Dim ary() As VariantReDim ary(rs.recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義Dim rsNum As Long '縦要素（レコードセット）の繰り返し用変数Dim fldNum As Long '横要素（フィールド）の繰り返し用変数Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す

getRsInArray_ADO = ary '配列を返す

GoTo Finally '正常に終了したら最終処理へジャンプ

Finally: '最終処理

以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。

On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim cn As Object 'ADOコネクション用オブジェクトの宣言Set cn = CurrentProject.Connection '現在開いているデータベースへ接続

cn.BeginTrans 'トランザクション開始

'実行Dim sql As VariantFor Each sql In sqlList 'SQL文リストをループ  cn.Execute sql '1行ずつ実行Next sql

cn.CommitTrans '確定

tryExecute_ADO = True '成功だった場合、関数の結果にTrueを入れるGoTo Finally '正常に終了したら最終処理へジャンプ

  テンプレートの利用（レコードの抽出）　　　　

SQLのSELECT構文を作成し、引数としてテンプレートへ渡して結果のレコードセットを二次配列の形で受け取るコードがこちらです。

SELECT構文の基本形は以下のようなものです。

SELECT フィールド1, フィールド2 FROM テーブル WHERE 条件;

'SQL作成Dim sql As Stringsql = "任意のSELECT構文"

'レコードセットを配列で取得Dim ary() As Variantary = getRsInArray_DAO(sql) '←DAOを利用する場合'ary = getRsInArray_ADO(sql) '←ADOを利用する場合

'空の配列だったら終了If isEmptyArray(ary) Then Exit Sub

'中身の確認Dim rsCount As LongrsCount = UBound(ary, 1) '第1要素（レコード）数Dim fldCount As LongfldCount = UBound(ary, 2) '第2要素（フィールド）数Dim x As Long, y As LongFor x = 0 To rsCount

取得部分は、DAO/ADOどちらか一方をお使いください。

配列を受け取った後、中身を確認する前に配列が空かどうかの判定も必要なので、以下の関数も任意のモジュールに追記してください。

On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示If UBound(tgtAry) >= 0 Then '配列に要素が存在したら  Exit Function  '終了（Falseを返す）End If

コードを実行すると、SELECT構文で抽出した配列の中身がVBEのイミディエイトウィンドウ（表示されていない場合は「表示」→「イミディエイトウィンドウ」）で確認できます。

取得した配列は、そのままコンボボックスやリストボックスのソースにセットすることができるほか、レコードの値を非連結で扱えるのでさまざまな実装に役立ちます。

  テンプレートの利用（レコードの挿入・更新・削除）　　　　

SQLのINSERT/UPDATE/DELETE構文をコレクションに格納し、引数としてテンプレートへ渡して結果をTrue/Falseの形で受け取るコードがこちらです。

それぞれの構文の基本形は以下のようなものです。

・INSERT INTO テーブル(フィールド1, フィールド2) VALUES(値1, 値2); ・UPDATE テーブル SET フィールド1=値1, フィールド2=値2  WHERE  条件; ・DELETE FROM テーブル  WHERE 条件;

複数のSQL文に対応するためコレクションに格納していますが、格納されるSQL文が1つでも動作します。数が多い場合はDoやForなどのループを使ってSQLを作成&コレクションへ格納していくとスマートです。

'コレクションの作成Dim sqlList As CollectionSet sqlList = New Collection

'SQL作成Dim sql As String 'SQL文用変数の宣言

sql = "任意のINSERT/UPDATE/DELETE構文" '1つ目のSQL文sqlList.Add sql 'コレクションへ追加

sql = "任意のINSERT/UPDATE/DELETE構文" '2つ目のSQL文sqlList.Add sql 'コレクションへ追加

'実行If tryExecute_DAO(sqlList) Then '処理が成功した場合 '←DAOを利用する場合'If tryExecute_ADO(sqlList) Then '処理が成功した場合 '←ADOを利用する場合  MsgBox "正常に終了しました", vbOKCancel + vbInformation, "終了" '終了メッセージEnd If

実行部分は、DAO/ADOどちらか一方をお使いください。

実行すると、問題が起きた場合は処理されずにエラー内容が、問題なく処理が終了した場合は終了メッセージが表示されます。

いかがだったでしょうか？次回はVBAでSQL文を扱う際のコツやポイントについてです。1行で済むような短い文ならば理解しやすいのですが、可読性のために改行したい、変数を使いたい、などの実用的なSQL組み込みのためのヒントをご紹介しますので、ぜひご期待ください。

第1話「イベント」を使いこなそう第2話 レイアウトで躓きやすいポイント第3話 マクロとVBAの違い